package com.drg.center.dao;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.fastjson.JSONObject;
import com.drg.center.entity.PatientCaseEsPO;
import com.drg.center.entity.param.DepartServiceEfficiencyDTO;
import com.drg.center.entity.result.*;
import com.drg.center.mapper.PatientCaseEsMapper;
import com.drg.center.utils.EsResultResolveUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.guaike.core.utils.BigDecimalUtil;
import com.guaike.core.utils.LocalDateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.Aggregations;
import org.elasticsearch.search.aggregations.BucketOrder;
import org.elasticsearch.search.aggregations.bucket.filter.ParsedFilter;
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramAggregationBuilder;
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramInterval;
import org.elasticsearch.search.aggregations.bucket.histogram.Histogram;
import org.elasticsearch.search.aggregations.bucket.nested.ParsedNested;
import org.elasticsearch.search.aggregations.bucket.range.Range;
import org.elasticsearch.search.aggregations.bucket.range.RangeAggregationBuilder;
import org.elasticsearch.search.aggregations.bucket.terms.ParsedStringTerms;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.metrics.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.elasticsearch.core.ElasticsearchOperations;
import org.springframework.data.elasticsearch.core.query.NativeSearchQuery;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.time.LocalDate;
import java.time.ZoneId;
import java.util.List;
import java.util.Map;

import static org.elasticsearch.index.query.QueryBuilders.boolQuery;

/**
 *  ES
 * @Author: zhangxilin
 * @Date: 2024/12/9
 */
@Slf4j
@Repository
public class EsDataMiningDAO {

    @Autowired
    private PatientCaseEsMapper patientCaseEsMapper;

    @Autowired
    private ElasticsearchOperations elasticsearchOperations;

    /**
     * 问题数据统计BY YEAR
     * DSL-013
      * @param departCode
     * @param startDate
     * @param endDate
     * @return
     */
    public List<MainDataColumnChartVO> getProblemDataByYear(String departCode, List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        queryBuilder.must(QueryBuilders.termQuery("isNormalCase", false));
        if(StringUtils.isNotBlank(departCode)){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", departCode));
    }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }


        DateHistogramAggregationBuilder dateHistogramAgg = AggregationBuilders.dateHistogram("year_agg")
                .field("outHospitalDate")
                .calendarInterval(DateHistogramInterval.YEAR)
                .format("yyyy")
                .timeZone(ZoneId.of("+08:00"))
                .subAggregation(AggregationBuilders.filter("diagnosis_problem_cases",
                        QueryBuilders.termQuery("isDiagnosisProblem", true))
                        .subAggregation(AggregationBuilders.count("count").field("caseCode")))
                .subAggregation(AggregationBuilders.filter("operation_problem_cases",
                        QueryBuilders.termQuery("isOperationProblem", true))
                        .subAggregation(AggregationBuilders.count("count").field("caseCode")))
                .subAggregation(AggregationBuilders.filter("traditional_problem_cases",
                        QueryBuilders.termQuery("isTraditionalProblem", true))
                        .subAggregation(AggregationBuilders.count("count").field("caseCode")));

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(dateHistogramAgg)
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();

        Histogram yearAgg = aggregations.get("year_agg");

        List<MainDataColumnChartVO> list = Lists.newArrayList();
        for (Histogram.Bucket bucket : yearAgg.getBuckets()) {
            String year = bucket.getKeyAsString();

            long diagnosisProblemCount = ((ParsedValueCount) ((ParsedFilter) bucket.getAggregations().get("diagnosis_problem_cases")).getAggregations().get("count")).getValue();
            long operationProblemCount = ((ParsedValueCount) ((ParsedFilter) bucket.getAggregations().get("operation_problem_cases")).getAggregations().get("count")).getValue();
            long traditionalProblemCount = ((ParsedValueCount) ((ParsedFilter) bucket.getAggregations().get("traditional_problem_cases")).getAggregations().get("count")).getValue();

            MainDataColumnChartVO vo = new MainDataColumnChartVO();
            vo.setKey(year);
            vo.setDiagnosis(Integer.parseInt(diagnosisProblemCount+""));
            vo.setOperation(Integer.parseInt(operationProblemCount+""));
            vo.setCmedical(Integer.parseInt(traditionalProblemCount+""));
            list.add(vo);
        }

        log.info("【EsDataMiningDAO.getProblemDataByYear】 list: {}", JSONObject.toJSONString(list));
        return list;
    }

    /**
     *  问题数据统计BY 科室
     *  DSL-014
     * @param departCode
     * @param startDate
     * @param endDate
     * @return
     */
    public List<DepartDataStatVO> getProblemDataByDepart(String departCode, List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(departCode)){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", departCode));
        }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("depart_agg").field("outDepartCode").size(10000).order(BucketOrder.aggregation("problem_cases", false))
                        .subAggregation(AggregationBuilders.filter("problem_cases", QueryBuilders.termQuery("isNormalCase", false))
                            .subAggregation(AggregationBuilders.count("caseCount").field("caseCode")))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.topHits("departName").size(1)
                                .fetchSource(new String[]{"outDepartName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms deptAgg = (ParsedStringTerms) aggregations.get("depart_agg");

        List<DepartDataStatVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : deptAgg.getBuckets()) {
            DepartDataStatVO vo = new DepartDataStatVO();
            //科室编码
            vo.setDepartCode(bucket.getKeyAsString());

            //科室名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("departName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDepartName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("outDepartName"));
            }

            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount(Integer.parseInt(caseCount.getValue()+""));

            //问题病案数量
            long problemCount = ((ParsedValueCount) ((ParsedFilter) bucket.getAggregations().get("problem_cases")).getAggregations().get("caseCount")).getValue();
            vo.setCaseProblemCount(Integer.parseInt(problemCount+""));

            //合格率
            vo.setQualifiedRate( BigDecimalUtil.calculatePercentage(vo.getCaseProblemCount(), vo.getCaseCount()) + "");
            list.add(vo);
        }

        log.info("【EsDataMiningDAO.getProblemDataByDepart】 list: {}", JSONObject.toJSONString(list));
        return list;
    }

    /**
     * 医疗服务质量统计
     * DSL-015
     * @param startDate
     * @param endDate
     * @return
     */
    public DataMedicalServerqualityVO getMedicalServerquality( List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                .addAggregation(AggregationBuilders.sum("totalCost").field("totalCost").format("0.00"))
                .addAggregation(AggregationBuilders.sum("totalInDays").field("inpatientDays"))
                .addAggregation(AggregationBuilders.filter("dealth_cases", QueryBuilders.termQuery("outType", "4"))
                    .subAggregation(AggregationBuilders.count("count").field("caseCode")))
                .addAggregation(AggregationBuilders.terms("dept_agg").field("outDepartCode").size(10000)
                        .subAggregation(AggregationBuilders.filter("overLengthIn",QueryBuilders.termQuery("isOverlengthIn", true))
                            .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("lowStandardIn",QueryBuilders.termQuery("isLowStandardIn", true))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("deathCount",QueryBuilders.termQuery("outType", "4"))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.topHits("departName").size(1)
                                .fetchSource(new String[]{"outDepartName"}, null))
                )
                .addAggregation(AggregationBuilders.terms("drg_agg").field("drgCode").size(10000)
                        .subAggregation(AggregationBuilders.filter("dealthCount", QueryBuilders.termQuery("outType", "4"))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.topHits("drgName").size(1)
                                .fetchSource(new String[]{"drgName"}, null))
                )

                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        ValueCount caseCount = aggregations.get("caseCount");
        Sum totalCostSum = aggregations.get("totalCost");
        Sum totalInDaysSum = aggregations.get("totalInDays");
        ParsedFilter dealthCaseFilter = aggregations.get("dealth_cases");
        ValueCount dealthValue = dealthCaseFilter.getAggregations().get("count");

        DataMedicalServerqualityVO vo = new DataMedicalServerqualityVO();
        vo.setCaseCount((int)caseCount.getValue());
        vo.setAvgDays(NumberUtil.div(totalInDaysSum.getValue()+"", caseCount.getValue()+"", 2));
        vo.setAvgCost(NumberUtil.div(totalCostSum.getValue()+"", caseCount.getValue()+"", 2));
        vo.setDealthCase((int)dealthValue.getValue());

        List<KeyValueVO> overLengthInSttList = Lists.newArrayList();
        List<KeyValueVO> lowStandardInSttList = Lists.newArrayList();
        List<KeyValueVO> deathSttList = Lists.newArrayList();

        Terms deptAgg = (ParsedStringTerms) aggregations.get("dept_agg");
        for (Terms.Bucket bucket : deptAgg.getBuckets()) {
            String key = "";
            //科室名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("departName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                key = (String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("outDepartName");
            }
            if(StringUtils.isBlank(key)){
                log.error("【EsDataMiningDAO.getMedicalServerquality】科室名称未找到, departCodeL {}", bucket.getKeyAsString());
                continue;
            }

            // 超长住院病例数
            ParsedFilter overLengthInFilter = bucket.getAggregations().get("overLengthIn");
            ValueCount overLengthInCount = overLengthInFilter.getAggregations().get("caseCount");
            if(overLengthInCount.getValue() >0 ){
                KeyValueVO keyValueVO = new KeyValueVO();
                keyValueVO.setKey(key);
                keyValueVO.setValue(overLengthInCount.getValue());
                overLengthInSttList.add(keyValueVO);
            }

            ParsedFilter lowStandardInFilter = bucket.getAggregations().get("lowStandardIn");
            ValueCount lowStandardInCount = lowStandardInFilter.getAggregations().get("caseCount");
            if(lowStandardInCount.getValue() > 0){
                KeyValueVO keyValueVO = new KeyValueVO();
                keyValueVO.setKey(key);
                keyValueVO.setValue(lowStandardInCount.getValue());
                lowStandardInSttList.add(keyValueVO);
            }

            ParsedFilter deathCountFilter = bucket.getAggregations().get("deathCount");
            ValueCount deathCountCount = deathCountFilter.getAggregations().get("caseCount");
            if(deathCountCount.getValue() >0){
                KeyValueVO keyValueVO = new KeyValueVO();
                keyValueVO.setKey(key);
                keyValueVO.setValue(deathCountCount.getValue());
                deathSttList.add(keyValueVO);
            }
        }
        vo.setOverLengthInSttList(overLengthInSttList);
        vo.setLowStandardInSttList(lowStandardInSttList);
        vo.setDeathSttList(deathSttList);

        List<KeyValueVO> drgSttList = Lists.newArrayList();
        Terms drgAgg = (ParsedStringTerms) aggregations.get("drg_agg");
        for (Terms.Bucket bucket : drgAgg.getBuckets()) {
            KeyValueVO keyValueVO = new KeyValueVO();

            //DRG名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("drgName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                keyValueVO.setKey((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("drgName"));
            }
            ParsedFilter dealthCountFilter = bucket.getAggregations().get("dealthCount");
            ValueCount deathCountCount = dealthCountFilter.getAggregations().get("caseCount");
            keyValueVO.setValue(deathCountCount.getValue());

            drgSttList.add(keyValueVO);
        }

        vo.setDrgSttList(drgSttList);

        log.info("【EsDataMiningDAO.getMedicalServerquality】vo: {}", JSONObject.toJSONString(vo));
        return vo;

    }

    /**
     * 科室服务效率
     * DSL-016
     * @param param
     * @return
     */
    public List<DepartServiceEfficiencyPageVO> getDepartServiceEfficiencyList(DepartServiceEfficiencyDTO param){

        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(param.getDepartCode())){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", param.getDepartCode()));
        }
        if(StringUtils.isNotBlank(param.getDoctorCode())){
            queryBuilder.must(QueryBuilders.termQuery("doctorCode", param.getDoctorCode()));
        }
        if(param.getStartDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(param.getStartDate())));
        }
        if(param.getEndDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(param.getEndDate().plusDays(1))));
        }


        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("doctorAgg")
                        .field("doctorCode")
                        .size(100000)
                        .order(BucketOrder.aggregation("caseCount", false))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.sum("totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("inpatientDays").field("inpatientDays"))
                        .subAggregation(AggregationBuilders.filter("overLengthIn",QueryBuilders.termQuery("isOverlengthIn", true))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("lowStandardIn",QueryBuilders.termQuery("isLowStandardIn", true))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.topHits("departName").size(2)
                                .fetchSource(new String[]{"doctorName", "outDepartName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorAgg = (ParsedStringTerms) aggregations.get("doctorAgg");

        List<DepartServiceEfficiencyPageVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : doctorAgg.getBuckets()) {
            DepartServiceEfficiencyPageVO vo = new DepartServiceEfficiencyPageVO();

            //医生编号
            vo.setDoctorCode(bucket.getKeyAsString());

            //科室名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("departName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDoctorName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("doctorName"));
                vo.setDepartName((String) departNameHits.getHits().getHits()[1].getSourceAsMap().get("outDepartName"));
            }

            //医疗总费用
            ParsedSum totalCostSum = bucket.getAggregations().get("totalCost");

            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount(Integer.parseInt(caseCount.getValue() + ""));


            //住院总天数
            ParsedSum inpatientDaysSum = bucket.getAggregations().get("inpatientDays");
            vo.setInHospitalAverage(NumberUtil.div(inpatientDaysSum.getValue()+"", caseCount.getValue()+"", 2));
            vo.setInHospitalAverageCost(NumberUtil.div(totalCostSum.getValue()+"", caseCount.getValue()+"", 2));


            // 超长住院病例数
            ParsedFilter overLengthInFilter = bucket.getAggregations().get("overLengthIn");
            ValueCount overLengthInCount = overLengthInFilter.getAggregations().get("caseCount");
            vo.setOverInHospitalCaseCount((int)overLengthInCount.getValue());

            ParsedFilter lowStandardInFilter = bucket.getAggregations().get("lowStandardIn");
            ValueCount lowStandardInCount = lowStandardInFilter.getAggregations().get("caseCount");
            vo.setLowRiskCaseCount((int)lowStandardInCount.getValue());
            list.add(vo);
        }

        log.info("【EsDataMiningDAO.getDepartServiceEfficiencyList】size: {}, list:{}", list.size(),  JSONObject.toJSONString(list));
        return list;

    }

    /**
     * 科室死亡率
     * DSL-017
     * @param param
     * @return
     */
    public List<DepartDieRatePageVO> getDepartDieRateList(DepartServiceEfficiencyDTO param){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(param.getDepartCode())){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", param.getDepartCode()));
        }
        if(StringUtils.isNotBlank(param.getDoctorCode())){
            queryBuilder.must(QueryBuilders.termQuery("doctorCode", param.getDoctorCode()));
        }
        if(param.getStartDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(param.getStartDate())));
        }
        if(param.getEndDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(param.getEndDate().plusDays(1))));
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("doctorAgg")
                        .field("doctorCode")
                        .size(100000)
                        .order(BucketOrder.aggregation("deathCount", false))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.cardinality("drgCount").field("drgCode"))
                        .subAggregation(AggregationBuilders.filter("deathCount",QueryBuilders.termQuery("outType", "4"))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("low-dealth-risk",QueryBuilders.termQuery("dealthRisk", 1))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("middle-low-dealth-risk",QueryBuilders.termQuery("dealthRisk", 2))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("middle-high-dealth-risk",QueryBuilders.termQuery("dealthRisk", 3))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.filter("high-dealth-risk",QueryBuilders.termQuery("dealthRisk", 4))
                                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        )
                        .subAggregation(AggregationBuilders.topHits("departName").size(2)
                                .fetchSource(new String[]{"doctorName","outDepartName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorAgg = (ParsedStringTerms) aggregations.get("doctorAgg");

        List<DepartDieRatePageVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : doctorAgg.getBuckets()) {
            DepartDieRatePageVO vo = new DepartDieRatePageVO();

            //医生编号
            vo.setDoctorCode(bucket.getKeyAsString());

            TopHits departNameHits = (TopHits) bucket.getAggregations().get("departName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDoctorName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("doctorName"));
                vo.setDepartName((String) departNameHits.getHits().getHits()[1].getSourceAsMap().get("outDepartName"));
            }

            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount(Integer.parseInt(caseCount.getValue() + ""));

            //drg病组数
            Cardinality drgCount = bucket.getAggregations().get("drgCount");
            vo.setDrgCount((int)drgCount.getValue());

            //死亡病例数
            ParsedFilter deathCountFilter = bucket.getAggregations().get("deathCount");
            ValueCount deathCount = deathCountFilter.getAggregations().get("caseCount");
            vo.setDieCaseCount((int)deathCount.getValue());

            //低风险死亡数
            ParsedFilter lowDeathCountFilter = bucket.getAggregations().get("low-dealth-risk");
            ValueCount lowDeathCount = lowDeathCountFilter.getAggregations().get("caseCount");
            vo.setLowDieCount((int)lowDeathCount.getValue());

            //中低风险死亡数
            ParsedFilter middleLowDeathCountFilter = bucket.getAggregations().get("middle-low-dealth-risk");
            ValueCount middleLowDeathCount = middleLowDeathCountFilter.getAggregations().get("caseCount");
            vo.setLowMiddleDieCount((int)middleLowDeathCount.getValue());

            //中高风险死亡数
            ParsedFilter middleHighDeathCountFilter = bucket.getAggregations().get("middle-high-dealth-risk");
            ValueCount middleHighDeathCoun = middleHighDeathCountFilter.getAggregations().get("caseCount");
            vo.setHighMiddleDieCount((int)middleHighDeathCoun.getValue());

            //高风险死亡数
            ParsedFilter highDeathCountFilter = bucket.getAggregations().get("high-dealth-risk");
            ValueCount highDeathCount = highDeathCountFilter.getAggregations().get("caseCount");
            vo.setHighDieCount((int)highDeathCount.getValue());

            list.add(vo);
        }
        log.info("【EsDataMiningDAO.DepartDieRateList】size: {}, list:{}", list.size(),  JSONObject.toJSONString(list));
        return list;
    }

    /**
     * 医疗服务能力-总览
     * DSL-018
     * @param departCode
     * @param startDate
     * @param endDate
     * @return
     */
    public MedicalServiceOverviewVO getMedicalServiceOverview(String departCode,List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(departCode)){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", departCode));
        }
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                .addAggregation(AggregationBuilders.sum("totalPoints").field("basisPoints").format("0.00"))
                .addAggregation(AggregationBuilders.nested("operation_level_counts", "operationVOS")
                        .subAggregation(AggregationBuilders.filter("level_1_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "1"))
                                .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                        )
                        .subAggregation(AggregationBuilders.filter("level_2_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "2"))
                                .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                        )
                        .subAggregation(AggregationBuilders.filter("level_3_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "3"))
                                .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                        )
                        .subAggregation(AggregationBuilders.filter("level_4_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "4"))
                                .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                        )
                )
                .addAggregation(AggregationBuilders.terms("drg_group").field("drgCode").size(100000)
                        .subAggregation(AggregationBuilders.count("cases_in_drg").field("caseCode")))
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();


        ValueCount caseCount = aggregations.get("caseCount");
        Sum totalPointsSum = aggregations.get("totalPoints");

        //手术信息
        ParsedNested operationLevelCounts = aggregations.get("operation_level_counts");
        ParsedFilter level1CasesFilter = operationLevelCounts.getAggregations().get("level_1_cases");
        ParsedFilter level2CasesFilter = operationLevelCounts.getAggregations().get("level_2_cases");
        ParsedFilter level3CasesFilter = operationLevelCounts.getAggregations().get("level_3_cases");
        ParsedFilter level4CasesFilter = operationLevelCounts.getAggregations().get("level_4_cases");

        ValueCount leve1_count = level1CasesFilter.getAggregations().get("leve_count");
        ValueCount leve2_count = level2CasesFilter.getAggregations().get("leve_count");
        ValueCount leve3_count = level3CasesFilter.getAggregations().get("leve_count");
        ValueCount leve4_count = level4CasesFilter.getAggregations().get("leve_count");


        MedicalServiceOverviewVO vo = new MedicalServiceOverviewVO();
        vo.setCaseCount((int)caseCount.getValue());
        vo.setTotalPointCount(BigDecimalUtil.parseBigDecimal(totalPointsSum.getValue()+""));
        vo.setOperationLevelOneOrTwoCount((int)(leve1_count.getValue()+leve2_count.getValue()));
        vo.setOperationLevelThreeOrFourCount((int)(leve3_count.getValue()+leve4_count.getValue()));

        ParsedStringTerms drgGroup = aggregations.get("drg_group");
        List<DrgAngleSttVO> drgAngleSttVOS = Lists.newArrayList();
        drgGroup.getBuckets().forEach(drgBucket ->{
            DrgAngleSttVO drgAngleSttVO = new DrgAngleSttVO();
            drgAngleSttVO.setDrgCode(((Terms.Bucket) drgBucket).getKeyAsString());
            ValueCount cases_in_drg_count = ((Terms.Bucket) drgBucket).getAggregations().get("cases_in_drg");
            drgAngleSttVO.setCaseCount((int)cases_in_drg_count.getValue());
            drgAngleSttVOS.add(drgAngleSttVO);
        });

        vo.setDrgStt(drgAngleSttVOS);
        log.info("【EsDataMiningDAO.DepartDieRateList】vo:{}",  JSONObject.toJSONString(vo));
        return vo;
    }


    /**
     *  医疗服务能力-rw分布
     *  DSL-019
     * @param departCode
     * @param startDate
     * @param endDate
     * @return
     */
    public DifficultCaseDistributeVO getDifficultCaseDistribute(String departCode,List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(departCode)){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", departCode));
        }
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }


        RangeAggregationBuilder rwRangeAgg = AggregationBuilders.range("case-by-rw-range")
                .field("rw")
                .addRange("0-0.6", 0, 0.6)
                .addRange("0.6-0.8",0.6, 0.8)
                .addRange("0.8-1.4",0.8, 1.4)
                .addRange("1.4-2.0",1.4, 2.0)
                .addRange("2.0-3.2",2.0, 3.2)
                .addUnboundedFrom("大于3.2",3.2)
                .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"));



        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(rwRangeAgg)
                .addAggregation(AggregationBuilders.terms("case_by_dept_agg").field("outDepartCode").size(5)
                        .subAggregation(rwRangeAgg)
                        .subAggregation(AggregationBuilders.topHits("departName").size(1)
                                .fetchSource(new String[]{"outDepartName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();

        // 解析 case-by-rw-range 聚合
        Range rwRanges = aggregations.get("case-by-rw-range");

        // 解析 case_by_dept_agg 聚合
        Terms deptBuckets = aggregations.get("case_by_dept_agg");

//        List<GroupDifficultCaseDistributeVO> deptCaseDistributeList = Lists.newArrayList();
        Map<String, List<Object>> dataMap = Maps.newHashMap();
        List<String> dataXList = Lists.newArrayList();
        List<String> segmentList = Lists.newArrayList();

        for (Terms.Bucket entry : deptBuckets.getBuckets()) {

            //科室名称
            TopHits departNameHits = (TopHits) entry.getAggregations().get("departName");
            String groupName =  EsResultResolveUtil.getName(departNameHits, 0, "outDepartName");

            dataXList.add(groupName);
            Range rwRangesByDept = entry.getAggregations().get("case-by-rw-range");
            setCaseDistributeList(rwRangesByDept, dataMap, segmentList);
        }

        DifficultCaseDistributeVO vo = new DifficultCaseDistributeVO();
        vo.setCaseDistributeList(getCaseDistributeList(rwRanges));
        vo.setComplexBarVO(getComplexBarVO(dataXList, segmentList, dataMap));
        log.info("【EsDataMiningDAO.getDifficultCaseDistribute】vo:{}",  JSONObject.toJSONString(vo));
        return vo;
    }

    private ComplexBarVO getComplexBarVO(List<String> dataX, List<String> segmentList, Map<String, List<Object>> dataMap){
        List<List<Object>> dataList = Lists.newArrayList();
        for(String sg: segmentList){
            List<Object> list = dataMap.get(sg);
            if(!CollectionUtils.isEmpty(list)){
                dataList.add(list);
            }
        }
        ComplexBarVO barVO = new ComplexBarVO();
        barVO.setDataList(dataList);
        barVO.setDataX(dataX);
        barVO.setSegments(segmentList);
        return barVO;
    }

    private void setCaseDistributeList(Range rwRanges , Map<String, List<Object>> dataMap, List<String> segmentList) {
        for (Range.Bucket entry : rwRanges.getBuckets()) {
            String key = entry.getKeyAsString();
            long docCount = ((ValueCount) entry.getAggregations().get("caseCount")).getValue();

            List<Object> datas = dataMap.get(key);
            if(CollectionUtils.isEmpty(datas)){
                datas = Lists.newArrayList();
            }
            datas.add(docCount);
            dataMap.put(key, datas);
            if(!segmentList.contains(key)){
                segmentList.add(key);
            }
        }

    }

    private List<KeyValueVO> getCaseDistributeList(Range rwRanges){
        List<KeyValueVO> caseDistributeList = Lists.newArrayList();
        for (Range.Bucket entry : rwRanges.getBuckets()) {
            String key = entry.getKeyAsString();
            long docCount = ((ValueCount) entry.getAggregations().get("caseCount")).getValue();

            KeyValueVO keyValueVO = new KeyValueVO();
            keyValueVO.setKey(key);
            keyValueVO.setValue(docCount);
            caseDistributeList.add(keyValueVO);
        }
        return caseDistributeList;
    }

    /**
     *  医疗服务能力-列表
     *  DSL-020
     * @param departCode
     * @param startDate
     * @param endDate
     * @return
     */
    public List<DepartAngleSttVO> getMedicalServiceByDept(String departCode,List<String> mainSearchDepartCodeList, LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(StringUtils.isNotBlank(departCode)){
            queryBuilder.must(QueryBuilders.termQuery("outDepartCode", departCode));
        }
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }
        if(!CollectionUtils.isEmpty(mainSearchDepartCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("outDepartCode", mainSearchDepartCodeList));
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("depart_agg")
                        .field("outDepartCode")
                        .size(10000)
                        .order(BucketOrder.aggregation("caseCount", false))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.sum("totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.cardinality("drgCount").field("drgCode"))
                        .subAggregation(AggregationBuilders.sum("totalPoints").field("basisPoints").format("0.00"))

                        .subAggregation(AggregationBuilders.nested("operation_level_counts", "operationVOS")
                                .subAggregation(AggregationBuilders.filter("level_1_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "1"))
                                        .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                                )
                                .subAggregation(AggregationBuilders.filter("level_2_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "2"))
                                        .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                                )
                                .subAggregation(AggregationBuilders.filter("level_3_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "3"))
                                        .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                                )
                                .subAggregation(AggregationBuilders.filter("level_4_cases", QueryBuilders.termQuery("operationVOS.operationLevel", "4"))
                                        .subAggregation(AggregationBuilders.count("leve_count").field("operationVOS.operationLevel"))
                                )
                        )
                        .subAggregation(AggregationBuilders.terms("drg_group").field("drgCode").size(100000)
                                .subAggregation(AggregationBuilders.count("cases_in_drg").field("caseCode")))
                        .subAggregation(AggregationBuilders.topHits("departName").size(1)
                                .fetchSource(new String[]{"outDepartName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms deptAgg = (ParsedStringTerms) aggregations.get("depart_agg");

        List<DepartAngleSttVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : deptAgg.getBuckets()) {
            DepartAngleSttVO vo = new DepartAngleSttVO();

            //科室编号
            vo.setDepartCode(bucket.getKeyAsString());

            //科室名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("departName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDepartName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("outDepartName"));
            }

            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount((int)caseCount.getValue());

            //总点数
            ParsedSum totalPointsSum = bucket.getAggregations().get("totalPoints");
            vo.setTotalPointCount(BigDecimalUtil.parseBigDecimal(totalPointsSum.getValueAsString()));

            //DRG组数
            Cardinality drgCount = bucket.getAggregations().get("drgCount");
            vo.setDrgCount((int)drgCount.getValue());

            //总点数
            ParsedSum totalCostSum = bucket.getAggregations().get("totalCost");
            vo.setTotalCost(BigDecimalUtil.parseBigDecimal(totalCostSum.getValueAsString()));

            //手术信息
            ParsedNested operationLevelCounts = bucket.getAggregations().get("operation_level_counts");
            ParsedFilter level1CasesFilter = operationLevelCounts.getAggregations().get("level_1_cases");
            ParsedFilter level2CasesFilter = operationLevelCounts.getAggregations().get("level_2_cases");
            ParsedFilter level3CasesFilter = operationLevelCounts.getAggregations().get("level_3_cases");
            ParsedFilter level4CasesFilter = operationLevelCounts.getAggregations().get("level_4_cases");

            ValueCount leve1_count = level1CasesFilter.getAggregations().get("leve_count");
            ValueCount leve2_count = level2CasesFilter.getAggregations().get("leve_count");
            ValueCount leve3_count = level3CasesFilter.getAggregations().get("leve_count");
            ValueCount leve4_count = level4CasesFilter.getAggregations().get("leve_count");

            vo.setOperationLevelOneOrTwoCount((int)(leve1_count.getValue()+leve2_count.getValue()));
            vo.setOperationLevelThreeOrFourCount((int)(leve3_count.getValue()+leve4_count.getValue()));

            ParsedStringTerms drgGroup = bucket.getAggregations().get("drg_group");
            List<DrgAngleSttVO> drgAngleSttVOS = Lists.newArrayList();
            drgGroup.getBuckets().forEach(drgBucket ->{
                DrgAngleSttVO drgAngleSttVO = new DrgAngleSttVO();
                drgAngleSttVO.setDrgCode(((Terms.Bucket) drgBucket).getKeyAsString());
                ValueCount cases_in_drg_count = ((Terms.Bucket) drgBucket).getAggregations().get("cases_in_drg");
                drgAngleSttVO.setCaseCount((int)cases_in_drg_count.getValue());
                drgAngleSttVOS.add(drgAngleSttVO);
            });

            vo.setDrgStt(drgAngleSttVOS);
            list.add(vo);
        }
        log.info("【EsDataMiningDAO.getMedicalServiceByDept】list:{}",  JSONObject.toJSONString(list));
        return list;
    }


}
